{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "6b873f4f",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "正在处理网址: https://www.ukpass.org/ranking/index-2-96-0.html\n",
      "正在处理网址: https://www.ukpass.org/ranking/index-2-80-0.html\n",
      "数据已保存到 data.xlsx\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "# 从文件中读取网址\n",
    "def read_urls_from_file(file_path):\n",
    "    with open(file_path, 'r') as file:\n",
    "        urls = file.readlines()\n",
    "        return [url.strip() for url in urls]\n",
    "\n",
    "# 爬取网页数据并解析\n",
    "def scrape_and_parse(url):\n",
    "    response = requests.get(url)\n",
    "    html_content = response.content\n",
    "    soup = BeautifulSoup(html_content, 'html.parser')\n",
    "    table = soup.find('table')\n",
    "    df = pd.read_html(str(table))[0]  # 利用 Pandas 直接解析 HTML 表格\n",
    "    return df\n",
    "\n",
    "# 将数据保存到Excel文件的不同sheet中\n",
    "def save_to_excel(dataframes, excel_file):\n",
    "    with pd.ExcelWriter(excel_file) as writer:\n",
    "        for i, df in enumerate(dataframes, 1):\n",
    "            df.to_excel(writer, sheet_name=f'Sheet{i}', index=False)\n",
    "\n",
    "# 主程序\n",
    "def main():\n",
    "    url_file = 'urls.txt'  # 包含网址的文件路径\n",
    "    urls = read_urls_from_file(url_file)\n",
    "\n",
    "    dataframes = []\n",
    "    for url in urls:\n",
    "        print(\"正在处理网址:\", url)\n",
    "        df = scrape_and_parse(url)\n",
    "        dataframes.append(df)\n",
    "\n",
    "    excel_file = 'data.xlsx'  # Excel 文件路径\n",
    "    save_to_excel(dataframes, excel_file)\n",
    "    print(f\"数据已保存到 {excel_file}\")\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "564a67ce",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d13f0445",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
